"""
将多个xlsx文件整合为一个json文件，然后通过ssh传输

@Author:czy(zhenyuan.chen@stu.xjtu.edu.cn)
@Date:2025/10/10
@Version:v1.3.0
"""
from datetime import datetime
import pandas as pd
import json
import glob
import os
from collections import defaultdict
import paramiko

def process_excel_files(excel_dir, file_pattern):
    # 构建完整的文件路径模式
    full_pattern = os.path.join(excel_dir, file_pattern)

    # 获取所有匹配的文件
    file_list = glob.glob(full_pattern)

    if not file_list:
        print(f"在目录 '{excel_dir}' 中未找到匹配模式 '{file_pattern}' 的文件")
        return None

    # 按传感器类型和ID分组数据
    sensor_data = defaultdict(lambda: defaultdict(list))

    for file_path in file_list:
        try:
            # 读取Excel文件
            df = pd.read_excel(file_path, sheet_name=0)

            # 从文件名提取传感器类型
            file_name = os.path.basename(file_path)
            parts = file_name.split('_')
            sensor_type = parts[1]

            # 从数据中获取传感器ID
            if not df.empty:
                sensor_id = df.iloc[0]['设备ID']

                # 将每行数据转换为字典并添加到对应传感器
                for _, row in df.iterrows():
                    record = row.to_dict()
                    sensor_data[sensor_type][sensor_id].append(record)

            print(f"已处理文件: {file_name}")

        except Exception as e:
            print(f"处理文件 {file_path} 时出错: {str(e)}")

    # 构建JSON结构
    result = {"sensors": []}

    for sensor_type, sensors in sensor_data.items():
        for sensor_id, data in sensors.items():
            result["sensors"].append({
                "sensor_type": sensor_type,
                "sensor_id": sensor_id,
                "data": data
            })

    return result


def save_to_json(data, json_path):
    # 确保输出目录存在
    output_dir = os.path.dirname(json_path)
    if output_dir and not os.path.exists(output_dir):
        os.makedirs(output_dir)

    with open(json_path, 'w', encoding='utf-8') as f:
        json.dump(data, f, ensure_ascii=False, indent=2)

    print(f"结果已保存到: {json_path}")

def delete_excel_files(excel_dir, file_pattern):
    """删除Excel文件"""
    # 构建完整的文件路径模式
    full_pattern = os.path.join(excel_dir, file_pattern)
    # 获取所有匹配的文件
    file_list = glob.glob(full_pattern)

    for file_path in file_list:
        os.remove(file_path)


def transfer_file(local_file, container_name, target_path,
                  remote_user="pi", remote_host="10.10.10.2",
                  password=None):
    try:
        # 创建SSH客户端
        ssh = paramiko.SSHClient()
        ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())

        print("正在连接到树莓派...")
        # 使用密码连接
        ssh.connect(remote_host, username=remote_user, password=password)

        # 读取文件内容
        print(f"读取本地文件: {local_file}")
        with open(local_file, 'rb') as f:
            file_content = f.read()

        # 构建容器内目标路径
        filename = os.path.basename(local_file)
        container_full_path = os.path.join(target_path, filename)

        # 执行docker命令传输文件
        command = f"docker exec -i {container_name} tee {container_full_path} > /dev/null"
        print(f"执行命令: {command}")

        stdin, stdout, stderr = ssh.exec_command(command)
        stdin.write(file_content)
        stdin.flush()
        stdin.channel.shutdown_write()

        # 检查执行状态
        exit_status = stdout.channel.recv_exit_status()

        if exit_status == 0:
            print(f"✓ 文件成功传输到容器: {container_name}:{container_full_path}")
        else:
            error = stderr.read().decode()
            print(f"✗ 传输失败: {error}")

        ssh.close()

    except Exception as e:
        print(f"✗ 错误: {str(e)}")


def main():
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")

    # 指定路径
    excel_dir = r"D:\Cloud_Edge_End\H960\update_h960-App_v1.4.6.3_b20250224\Data"
    json_path = fr"D:\Cloud_Edge_End\H960\ExportData\SensorData\sensor_data_{timestamp}.json"
    file_pattern = "COM3_*.xlsx"

    # 处理Excel文件
    json_data = process_excel_files(excel_dir, file_pattern)

    # 删除Excel文件
    delete_excel_files(excel_dir, file_pattern)

    if json_data is not None:
        # 保存为JSON文件
        save_to_json(json_data, json_path)
        print("数据处理完成!")

        container_name = "jupyterlab"
        target_path = "/root/test01/"
        password = "000000"
        remote_host = "10.10.10.2"
        transfer_file(
            local_file=json_path,
            container_name=container_name,
            target_path=target_path,
            remote_host=remote_host,
            password=password
        )
        print("数据传输完成！")
    else:
        print("未找到任何可处理的数据文件")


if __name__ == "__main__":
    main()
